
# +-----------------------------------+
# | IMP Paper data prep and graphics  |
# +-----------------------------------+
### setting up 

rm(list=ls(all=T))

# change the folder accordingly 
setwd("/Users/safrankolker/Dropbox/IMP Paper/IMP Paper Code final")

folder <- c("/Users/safrankolker/Dropbox/IMP Paper/IMP Paper Code final/IMP Paper Graphics")   

library(reshape2)
library(ggpubr)
library(ggplot2)
library(purrr)
library(zoo)
library(stringr)
library(readxl)
library(MetBrewer)
library(forcats)
library(openxlsx) 
library(writexl)
library(data.table)
library(countrycode)
library(dplyr)
library(tidyr)
library(ggpattern)


# +--------------+
# |  Define OFCs |
# +--------------+

# offshorelist from Garcia-Bernardo et al. 2017 
offshorelist_iso2c <- c("VG", "TW", "JE", "BM", "KY", "WS", "LI", "CW", "MH", "MT", "MU", 
                        "LU", "NR", "CY", "SC", "BS", "BZ", "GI", "AI", "LR", "VC", "GY", "HK",  
                        "NL", "GB", "CH", "SG", "IE")

offshorelist <- countrycode(offshorelist_iso2c, origin = 'iso2c', destination = 'iso3c')

# +------------+
# |  Euroloans |
# +------------+

### calculate Euroloans from BIS LBS

# BIS Locational Banking Statistics https://data.bis.org/topics/LBS
# for details see https://www.bis.org/statistics/dsd_lbs.pdf 

LBS <- data.table(read.csv("/Users/safrankolker/Dropbox/IMP Paper/CO analysis/WS_LBS_D_PUB_csv_col.csv"),
                  header=T, skip=0, sep=",")

# check reporting countries
suppressWarnings(
  LBS[, iso3c := countrycode(Reporting.country, "country.name", "iso3c")]
)

unique(LBS[is.na(iso3c), Reporting.country])

#change countrycodes for reporting country and counterparty country to iso3c
LBS$iso3c <-countrycode (LBS$L_REP_CTY, "iso2c", "iso3c")
LBS$iso3c_cparty <-countrycode (LBS$L_CP_COUNTRY, "iso2c", "iso3c")
# some values are not matched unambiguously: 5A, 5C, AN/Users/safrankolker/Desktop/IMP Paper Code/IMP Paper Graphics.

# delete NAs for iso3c
LBS <- LBS[ !is.na(iso3c)]
# LBS[,country := countrycode(iso3, "iso3c", "country.name")]


suppressWarnings(
  LBS[,iso3c_cparty:=countrycode(Counterparty.country,"country.name", "iso3c")]
)
#countrycode does not recognize:
unique(LBS[is.na(iso3c_cparty),Counterparty.country])


# keep the 'all countries' counterparty for other information: 
LBS_aggregated <- LBS[Counterparty.country == "All countries (total)"]


#some more cleanup:
LBS[,Frequency:=NULL]             # all quarterly
LBS[,FREQ:=NULL]             # all quarterly

LBS_aggregated[,Frequency:=NULL]             # all quarterly
LBS_aggregated[,FREQ:=NULL]             # all quarterly


# +-----------------+
# | AGGREGATED DATA |
# +-----------------+

# aggregate data is used to calculate the dollar share of each countries' assets and liabilities
names(LBS_aggregated)
unique(LBS_aggregated$iso3c)

# melt data
LBS_aggregated_m <- reshape2::melt(LBS_aggregated, 
                                   id.vars = c("iso3c",
                                               "Balance.sheet.position", 
                                               "Type.of.instruments",
                                               "Currency.denomination",
                                               "Currency.type.of.reporting.country",
                                               "Parent.country",
                                               "Type.of.reporting.institutions",
                                               "Counterparty.sector",
                                               "Position.type"),
                                   measure.vars=c(names(LBS)[grepl("X",names(LBS))]),
                                   variable.name = "time",
                                   value.name = "lbs")


LBS_aggregated_m <- as.data.table(LBS_aggregated_m )
# yearquarter should be yearqtr:
LBS_aggregated_m[,yearquarter := substr(as.character(time),2,99)]
LBS_aggregated_m[,yearquarter := as.yearqtr(yearquarter,format="%Y.Q%q")]
LBS_aggregated_m[, time := NULL]


# we keep both balance sheet positions                            

# all instruments
LBS_aggregated_m <- LBS_aggregated_m[Type.of.instruments == "All instruments"]
LBS_aggregated_m[, Type.of.instruments := NULL]

# Currency denomination we keep but we only care about foreign currency type an 'all currencies'
LBS_aggregated_m <- LBS_aggregated_m[Currency.type.of.reporting.country == "Foreign currency (ie currencies foreign to bank location country)"]
# we loose unallocated currencies here
LBS_aggregated_m[,Currency.type.of.reporting.country := NULL]

# Parent country is only all countries:
LBS_aggregated_m[, Parent.country := NULL]

# same for type of reporting institution
LBS_aggregated_m[, Type.of.reporting.institutions := NULL]


# counterparty sector for currency breakdown doesn't exist, only for all currencies
LBS_aggregated_m[, Counterparty.sector := NULL]

# and we only care about cross border positions
LBS_aggregated_m <- LBS_aggregated_m[Position.type == "Cross-border"]
LBS_aggregated_m[, Position.type := NULL]
# https://www.bis.org/statistics/dsd_lbs.pdf (p. 21)

names(LBS_aggregated_m)
unique(LBS_aggregated_m$Position.type)


# create new df with only dollar positions
LBS_aggregated_dollars <- LBS_aggregated_m[Currency.denomination == "US dollar",]

# aggregate values by year and balance sheet position to get aggregate usd values
LBS_aggregated_sum <- aggregate(lbs ~ yearquarter + Balance.sheet.position , data = LBS_aggregated_dollars, FUN = sum)

# rename columns 
names(LBS_aggregated_sum) <- c("year_quarter", "position", "Amount")

# adjust yearquarter
LBS_aggregated_sum$year_quarter <- gsub("X", "", LBS_aggregated_sum$year_quarter)  
LBS_aggregated_sum$year_quarter <- gsub(" Q1", ".00", LBS_aggregated_sum$year_quarter)  
LBS_aggregated_sum$year_quarter <- gsub(" Q2", ".25", LBS_aggregated_sum$year_quarter)  
LBS_aggregated_sum$year_quarter <- gsub(" Q3", ".50", LBS_aggregated_sum$year_quarter)  
LBS_aggregated_sum$year_quarter <- gsub(" Q4", ".75", LBS_aggregated_sum$year_quarter) 
LBS_aggregated_sum$year_quarter <- as.numeric(LBS_aggregated_sum$year_quarter )


# Transform the data
LBS_aggregated_sum <- LBS_aggregated_sum %>%
  spread(key = position, value = Amount) %>%
  group_by(year_quarter)

names(LBS_aggregated_sum) <- c("yearquarter", "total_usd_claims", "total_usd_liabs")





## cast total aggregate set 

LBS_aggregated <- dcast(LBS_aggregated_m, iso3c + yearquarter ~
                          Balance.sheet.position + Currency.denomination,
                        value.var="lbs", fun.aggregate = sum)

# rename 

setnames(LBS_aggregated,"Total claims_All currencies",
         "claims_allcur")

setnames(LBS_aggregated,"Total claims_US dollar",
         "claims_usd")

setnames(LBS_aggregated,"Total liabilities_All currencies",
         "liabs_allcur")

setnames(LBS_aggregated,"Total liabilities_US dollar",
         "liabs_usd")




### these are the aggregated datasets

LBSstocks_aggregated_dollar_sum <- copy(LBS_aggregated_sum)
# save(LBSstocks_aggregated_dollar_sum, file = "/Users/safrankolker/Dropbox/8 Jacob Andrea Paper/3_Data/LBSstocks_aggregated_dollar_sum_2023.RData")


# change year format
LBS_aggregated$yearquarter <- gsub(" Q1", ".00", LBS_aggregated$yearquarter)  
LBS_aggregated$yearquarter <- gsub(" Q2", ".25", LBS_aggregated$yearquarter)  
LBS_aggregated$yearquarter <- gsub(" Q3", ".50", LBS_aggregated$yearquarter)  
LBS_aggregated$yearquarter <- gsub(" Q4", ".75", LBS_aggregated$yearquarter) 
LBS_aggregated$year_quarter <- as.numeric(LBS_aggregated$yearquarter )

# rename
LBSstocks_aggregated <- copy(LBS_aggregated)

# adjust yearquarter
LBSstocks_aggregated$yearquarter <- gsub(" Q1", ".00", LBSstocks_aggregated$yearquarter)  
LBSstocks_aggregated$yearquarter <- gsub(" Q2", ".25", LBSstocks_aggregated$yearquarter)  
LBSstocks_aggregated$yearquarter <- gsub(" Q3", ".50", LBSstocks_aggregated$yearquarter)  
LBSstocks_aggregated$yearquarter <- gsub(" Q4", ".75", LBSstocks_aggregated$yearquarter) 
LBSstocks_aggregated$year_quarter <- as.numeric(LBSstocks_aggregated$yearquarter )


### create bilateral data to find OFCs

names(LBS)
# melt 
LBS_bilateral <- reshape2::melt(LBS, 
                                id.vars = c("iso3c",
                                            "iso3c_cparty",
                                            "Measure", 
                                            "Balance.sheet.position", 
                                            "Type.of.instruments",
                                            "Currency.denomination",
                                            "Currency.type.of.reporting.country",
                                            "Parent.country",
                                            "Type.of.reporting.institutions",
                                            "Counterparty.sector",
                                            "Position.type"),
                                measure.vars=c(names(LBS)[grepl("X",names(LBS))]),
                                variable.name = "time",
                                value.name = "lbs")


# yearquarter should be yearqtr:
LBS_bilateral <- as.data.table(LBS_bilateral)
LBS_bilateral[,yearquarter := substr(as.character(time),2,99)]
LBS_bilateral[,yearquarter := as.yearqtr(yearquarter,format="%Y.Q%q")]
LBS_bilateral[, time := NULL]
# for now we use the full sample here and create unbalanced and balanced
# aggregated series

LBS_bilateral[, Currency.denomination := NULL]

LBS_bilateral[,Currency.type.of.reporting.country:=NULL]

#same for parent country:
# unique(LBS$Parent.country)
LBS_bilateral[, Parent.country:=NULL]

# same for type of reporting institutions:
# unique(LBS$Type.of.reporting.institutions) #not on bilateral level
LBS_bilateral[,Type.of.reporting.institutions:=NULL]

# two counterparty sectors:
unique(LBS$Counterparty.sector)
# All sectors
# Non-banks, total
# we are interested only in all counterparty sectors 
LBS_bilateral <- LBS_bilateral[Counterparty.sector == "All sectors"]
LBS_bilateral[,Counterparty.sector:=NULL]

# all positions cross border in bilateral data:
unique(LBS$Position.type)
unique(LBS_bilateral$Position.type)
LBS_bilateral[,Position.type:=NULL]


LBSstocks_bilateral <- LBS_bilateral[Measure == "Amounts outstanding / Stocks"]
LBSstocks_bilateral[, Measure := NULL]


LBSstocks_bilateral <- dcast(LBSstocks_bilateral, iso3c + iso3c_cparty + yearquarter ~
                               Balance.sheet.position + Type.of.instruments,
                             value.var="lbs", fun.aggregate = sum)


# these are the reports that are available bilaterally:
countrycode(setdiff(unique(LBS$iso3c),offshorelist),"iso3c","country.name")
# these are the OFCs that report bilaterally
countrycode(c(unique(LBS$iso3c))[unique(LBS$iso3c)%in%offshorelist], "iso3c", "country.name")
 

#rename, so the names are structured like:
# claims_all_all
# claims_all_nonbanks

setnames(LBSstocks_bilateral,"Total claims_All instruments",
         "claims_all_all")
setnames(LBSstocks_bilateral,"Total claims_Loans and deposits",
         "claims_loandeps_all")

setnames(LBSstocks_bilateral,"Total liabilities_All instruments",
         "liabs_all_all")
setnames(LBSstocks_bilateral,"Total liabilities_Loans and deposits",
         "liabs_loandeps_all")


names(LBSstocks_bilateral)


# adjust yearquarter
LBSstocks_bilateral$yearquarter <- gsub(" Q1", ".00", LBSstocks_bilateral$yearquarter)  
LBSstocks_bilateral$yearquarter <- gsub(" Q2", ".25", LBSstocks_bilateral$yearquarter)  
LBSstocks_bilateral$yearquarter <- gsub(" Q3", ".50", LBSstocks_bilateral$yearquarter)  
LBSstocks_bilateral$yearquarter <- gsub(" Q4", ".75", LBSstocks_bilateral$yearquarter) 
LBSstocks_aggregated$yearquarter <- as.numeric(LBSstocks_aggregated$yearquarter)
LBSstocks_bilateral$yearquarter <- as.numeric(LBSstocks_bilateral$yearquarter)
LBSstocks_bilateral$year_quarter <- LBSstocks_bilateral$yearquarter


#save(LBSstocks_bilateral, file = "/Users/safrankolker/Dropbox/8 Jacob Andrea Paper/3_Data/LBSstocks_bilateral_2023.RData")




# +----------------------------------+
# | CALCULATE EUROLOANS THROUGH LOOP |
# +----------------------------------+

# for which countries do we need to loop ?
unique(LBSstocks_bilateral$iso3c)
unique(LBSstocks_aggregated$iso3c)



# Define the list of sample countries for IMP paper 
countries <- c("GBR", "DEU", "JPN", "FRA", "CHE", "BRA", "KOR", "GRC")


# Create an empty data frame to store the aggregated values
aggregated_df_offshore <- data.frame(country = character(0), yearquarter = character(0), off_usd_c_aggregated = numeric(0), off_usd_l_aggregated = numeric(0))


# Loop over the countries
for (country in countries) {
  # find country
  LBS_country <- LBSstocks_aggregated[LBSstocks_aggregated$iso3c == country, ]
  LBS_country$share_usd_claims <- LBS_country$claims_usd / LBS_country$claims_allcur
  LBS_country$share_usd_liabs <- LBS_country$liabs_usd / LBS_country$liabs_allcur
  
  # find stocks from country to offshore centers
  LBS_country_offshore <- LBSstocks_bilateral[LBSstocks_bilateral$iso3c == country & is.element(LBSstocks_bilateral$iso3c_cparty, offshorelist), ]
  
  # set NAs to NA
  LBS_country_offshore[LBS_country_offshore == -Inf] <- NA
  LBS_country_offshore[LBS_country_offshore == "NaN"] <- NA
  
  # rename
  LBS_country_offshore$claims_ofc <- LBS_country_offshore$claims_all_all
  # make liabs negative
  LBS_country_offshore$liabs_ofc <- LBS_country_offshore$liabs_all_all * (-1)
  
  # sum all offshore stocks
  LBS_country_offshore <- aggregate(cbind(claims_ofc, liabs_ofc) ~ yearquarter, data = LBS_country_offshore, FUN = sum)
  
  # merge currency share and offshore level
  LBS_country <- merge(LBS_country, LBS_country_offshore, by = "yearquarter", all.x = TRUE)
  
  # multiply offshore liabs and claims with share of currency x in total liabs
  LBS_country$off_usd_c <- (LBS_country$liabs_ofc * LBS_country$share_usd_liabs)
  LBS_country$off_usd_l <- (LBS_country$claims_ofc * LBS_country$share_usd_claims)
  
  # Loop over the unique yearquarters for the country
  for (qtr in unique(LBS_country$yearquarter)) {
    # Filter the data for the current yearquarter
    qtr_data <- LBS_country[LBS_country$yearquarter == qtr, ]
    
    # Aggregate off_usd_c and off_usd_l for the yearquarter
    off_usd_c_aggregated <- sum(qtr_data$off_usd_c, na.rm = TRUE)
    off_usd_l_aggregated <- sum(qtr_data$off_usd_l, na.rm = TRUE)
    
    # Create a temporary data frame for the current yearquarter
    qtr_df <- data.frame(country = country, yearquarter = qtr, off_usd_c_aggregated, off_usd_l_aggregated)
    
    # Append the yearquarter data to the aggregated data frame
    aggregated_df_offshore <- rbind(aggregated_df_offshore, qtr_df)
  }
  
  rm(LBS_country_offshore, LBS_country)
}


### now, do all for US 

US_list = c( "USA")

# Create an empty data frame to store the aggregated values
aggregated_df_onshore <- data.frame(country = character(0), yearquarter = character(0), ons_usd_c_aggregated = numeric(0), ons_usd_l_aggregated = numeric(0))



# Loop over the countries
for (country in countries) {
  # find country
  LBS_country <- LBSstocks_aggregated[LBSstocks_aggregated$iso3c == country, ]
  LBS_country$share_usd_claims <- LBS_country$claims_usd / LBS_country$claims_allcur
  LBS_country$share_usd_liabs <- LBS_country$liabs_usd / LBS_country$liabs_allcur
  
  # find stocks from country to US
  LBS_country_onshore <- LBSstocks_bilateral[LBSstocks_bilateral$iso3c == country & is.element(LBSstocks_bilateral$iso3c_cparty, US_list), ]
  
  # set NAs to NA
  LBS_country_onshore[LBS_country_onshore == -Inf] <- NA
  LBS_country_onshore[LBS_country_onshore == "NaN"] <- NA
  
  # rename
  LBS_country_onshore$claims_US <- LBS_country_onshore$claims_all_all
  # make liabs negative
  LBS_country_onshore$liabs_US <- LBS_country_onshore$liabs_all_all * (-1)
  
  # sum all US stocks
  LBS_country_onshore <- aggregate(cbind(claims_US, liabs_US) ~ yearquarter, data = LBS_country_onshore, FUN = sum)
  
  # merge currency share and onshore level
  LBS_country <- merge(LBS_country, LBS_country_onshore, by = "yearquarter", all.x = TRUE)
  
  # multiply offshore liabs and claims with share of currency x in total liabs
  LBS_country$ons_usd_l <- (LBS_country$liabs_US * LBS_country$share_usd_liabs)
  LBS_country$ons_usd_c <- (LBS_country$claims_US * LBS_country$share_usd_claims)
  
  # Loop over the unique yearquarters for the country
  for (qtr in unique(LBS_country$yearquarter)) {
    # Filter the data for the current yearquarter
    qtr_data <- LBS_country[LBS_country$yearquarter == qtr, ]
    
    # Aggregate off_usd_c and off_usd_l for the yearquarter
    ons_usd_c_aggregated <- sum(qtr_data$ons_usd_c, na.rm = TRUE)
    ons_usd_l_aggregated <- sum(qtr_data$ons_usd_l, na.rm = TRUE)
    
    # Create a temporary data frame for the current yearquarter
    qtr_df <- data.frame(country = country, yearquarter = qtr, ons_usd_c_aggregated, ons_usd_l_aggregated)
    
    # Append the yearquarter data to the aggregated data frame
    aggregated_df_onshore <- rbind(aggregated_df_onshore, qtr_df)
  }
  
  rm(LBS_country_onshore, LBS_country)
}




### now, do all for foreign dollars 

# list with all OFCs plus US
non_foreign_v <- (c (offshorelist, "USA"))

# list with all counterparty countries
all_cps <- unique (LBSstocks_bilateral$iso3c_cparty)

# new list with all counterparty countries except OFCs and US
foreign_list <- all_cps [!all_cps  %in% non_foreign_v ]


# Create an empty data frame to store the aggregated values
aggregated_df_foreign <- data.frame(country = character(0), yearquarter = character(0), on_foreignd_c_aggregated = numeric(0), on_foreignd_l_aggregated = numeric(0))



# Loop over the countries
for (country in countries) {
  # find country
  LBS_country <- LBSstocks_aggregated[LBSstocks_aggregated$iso3c == country, ]
  LBS_country$share_usd_claims <- LBS_country$claims_usd / LBS_country$claims_allcur
  LBS_country$share_usd_liabs <- LBS_country$liabs_usd / LBS_country$liabs_allcur
  
  # find stocks from country to offshore centers
  LBS_country_foreign <- LBSstocks_bilateral[LBSstocks_bilateral$iso3c == country & is.element(LBSstocks_bilateral$iso3c_cparty, foreign_list), ]
  
  # set NAs to NA
  LBS_country_foreign[LBS_country_foreign == -Inf] <- NA
  LBS_country_foreign[LBS_country_foreign == "NaN"] <- NA
  
  # rename
  LBS_country_foreign$claims_foreign <- LBS_country_foreign$claims_all_all
  # make liabs negative
  LBS_country_foreign$liabs_foreign <- LBS_country_foreign$liabs_all_all * (-1)
  
  # sum all foreign stocks
  LBS_country_foreign <- aggregate(cbind(claims_foreign, liabs_foreign) ~ yearquarter, data = LBS_country_foreign, FUN = sum)
  
  # merge currency share and offshore level
  LBS_country <- merge(LBS_country, LBS_country_foreign, by = "yearquarter", all.x = TRUE)
  
  # multiply offshore liabs and claims with share of currency x in total liabs
  LBS_country$for_usd_c <- (LBS_country$liabs_foreign * LBS_country$share_usd_liabs)
  LBS_country$for_usd_l <- (LBS_country$claims_foreign * LBS_country$share_usd_claims)
  
  # Loop over the unique yearquarters for the country
  for (qtr in unique(LBS_country$yearquarter)) {
    # Filter the data for the current yearquarter
    qtr_data <- LBS_country[LBS_country$yearquarter == qtr, ]
    
    # Aggregate off_foreignd_c and off_foreignd_l for the yearquarter
    for_usd_c_aggregated <- sum(qtr_data$for_usd_c, na.rm = TRUE)
    for_usd_l_aggregated <- sum(qtr_data$for_usd_l, na.rm = TRUE)
    
    # Create a temporary data frame for the current yearquarter
    qtr_df <- data.frame(country = country, yearquarter = qtr, for_usd_c_aggregated, for_usd_l_aggregated)
    
    # Append the yearquarter data to the aggregated data frame
    aggregated_df_foreign <- rbind(aggregated_df_foreign, qtr_df)
  }
  
  rm(LBS_country_foreign, LBS_country)
}



#### tie together onshore dollars, OFC Eurodollars ('offshore dollars') and non-OFC Eurodollars ('foreign dollars')

#rm(IMP_data)
IMP_data <- cbind (aggregated_df_onshore, aggregated_df_offshore, aggregated_df_foreign )

# correct some columns 

IMP_data$off_usd_c_aggregated <- IMP_data$off_usd_c_aggregated  * (-1)
IMP_data$off_usd_l_aggregated <- IMP_data$off_usd_l_aggregated  * (-1)
IMP_data$for_usd_c_aggregated <- IMP_data$for_usd_c_aggregated  * (-1)
IMP_data$for_usd_l_aggregated <- IMP_data$for_usd_l_aggregated  * (-1)

# remove duplicate columns 
IMP_data <- IMP_data[, c(1,2,3,4,7,8,11,12)]

# year as numeric 
IMP_data$yearquarter <- as.numeric (IMP_data$yearquarter)

# calculate offshore-to-onshore-ratio 
IMP_data$off_ons_ratio_c <- IMP_data$off_usd_c_aggregated / IMP_data$ons_usd_c_aggregated

# calculate onshore-to-offshore-ratio 
IMP_data$ons_off_ratio_c <- IMP_data$ons_usd_c_aggregated / IMP_data$off_usd_c_aggregated

# calculate offshore-to-foreign-ratio 
IMP_data$off_for_ratio_c <- IMP_data$off_usd_c_aggregated / IMP_data$for_usd_c_aggregated

# calculate foreign-to-offshore-ratio 
IMP_data$for_off_ratio_c <- IMP_data$for_usd_c_aggregated / IMP_data$off_usd_c_aggregated


#save IMP_data as excel file 

write.xlsx(IMP_data, "/Users/safrankolker/Desktop/IMP Paper Code/IMP_data_v01.xlsx")



### start here if you only want to reproduce the graphics 

# rm(list=ls(all=T))

# offshorelist from Garcia-Bernardo et al. 2017 
offshorelist_iso2c <- c("VG", "TW", "JE", "BM", "KY", "WS", "LI", "CW", "MH", "MT", "MU", 
                        "LU", "NR", "CY", "SC", "BS", "BZ", "GI", "AI", "LR", "VC", "GY", "HK",  
                        "NL", "GB", "CH", "SG", "IE")

offshorelist <- countrycode(offshorelist_iso2c, origin = 'iso2c', destination = 'iso3c')



IMP_data <- read.xlsx("/Users/safrankolker/Dropbox/IMP Paper/CO analysis/IMP_data_v01.xlsx")

# make everything positive again 

IMP_data$off_usd_l_aggregated <- IMP_data$off_usd_l_aggregated  * (-1)
IMP_data$for_usd_l_aggregated <- IMP_data$for_usd_l_aggregated  * (-1)
IMP_data$ons_usd_l_aggregated <- IMP_data$ons_usd_l_aggregated  * (-1)

# rename variables for plotting

setnames(IMP_data,"ons_usd_c_aggregated",
         "Onshore Dollar (claims)")
setnames(IMP_data,"for_usd_c_aggregated",
         "Offshore Dollar, AOC (claims)")
setnames(IMP_data,"off_usd_c_aggregated",
         "Offshore Dollar, OFC (claims)")
setnames(IMP_data,"ons_usd_l_aggregated",
         "Onshore Dollar (liabilities)")
setnames(IMP_data,"for_usd_l_aggregated",
         "Offshore Dollar, AOC (liabilities)")
setnames(IMP_data,"off_usd_l_aggregated",
         "Offshore Dollar, OFC (liabilities)")

  
# define colours, legend and patterns for plots

colours <- c("grey95", "grey70", 
             "grey95", "grey70", 
             "grey95", "grey70")


patterns <- c( "crosshatch", "crosshatch", "stripe",  "stripe", "regular_polygon", "regular_polygon")  


titles <- c(  "Offshore Dollar, OFC (claims)", "Offshore Dollar, OFC (liabilities)",  
              "Offshore Dollar, AOC (claims)","Offshore Dollar, AOC (liabilities)", 
              "Onshore Dollar (claims)" , "Onshore Dollar (liabilities)")


#### BRAZIL ####

# dataset with only BRA
IMP_data_BRA <- IMP_data %>%
  filter(country == "BRA")

#melt to long format
IMP_data_BRA_m <- reshape2::melt(IMP_data_BRA, id.vars = c( "yearquarter", "country"), variable.name = "variable", value.name = "value")

#create groups
IMP_data_BRA_m$Instrument <- ordered( IMP_data_BRA_m$variable, levels = titles)

# set zero values to NA
IMP_data_BRA_m[IMP_data_BRA_m ==0] <- NA

# delete NAs
IMP_data_BRA_m <- IMP_data_BRA_m %>% filter(!is.na(Instrument))
IMP_data_BRA_m <- IMP_data_BRA_m %>% filter(!is.na(value))

# normalize BRA data manually to prevent error 'subscript out of bounds'
IMP_data_BRA_m_normalized <- IMP_data_BRA_m %>%
  group_by(yearquarter) %>%
  mutate(value_norm = value / sum(value)) %>%
  ungroup()


# Create an area plot with patterns
Brazil <- ggplot(IMP_data_BRA_m_normalized, aes(y = value_norm, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
 labs(y = NULL, x = NULL, title = "Brazil") +
  theme_minimal()


Brazil_stack <- ggplot(IMP_data_BRA_m, aes(y = value/1000, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1, 
                     position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Brazil") +
  theme_minimal()



#### Germany ####

# dataset with only DEU
IMP_data_DEU <- IMP_data %>%
  filter(country == "DEU")

#melt to long format
IMP_data_DEU_m <- reshape2::melt(IMP_data_DEU, id.vars = c( "yearquarter", "country"), variable.name = "variable", value.name = "value")

#create groups
IMP_data_DEU_m$Instrument <- ordered( IMP_data_DEU_m$variable,  levels = titles)

# set zero values to NA
IMP_data_DEU_m[IMP_data_DEU_m ==0] <- NA

# delete NAs
IMP_data_DEU_m <- IMP_data_DEU_m %>% filter(!is.na(Instrument))
IMP_data_DEU_m <- IMP_data_DEU_m %>% filter(!is.na(value))

# normalize KOR data manually to prevent error 'subscript out of bounds'
IMP_data_DEU_m_normalized <- IMP_data_DEU_m %>%
  group_by(yearquarter) %>%
  mutate(value_norm = value / sum(value)) %>%
  ungroup()


# Create an area plot with patterns
Germany <- ggplot(IMP_data_DEU_m_normalized, aes(y = value_norm, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1983,2023,10), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Germany") +
  theme_minimal()

Germany_stack <- ggplot(IMP_data_DEU_m, aes(y = value/1000, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1983,2023,10), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Germany") +
  theme_minimal()


#### Switzerland ####

# dataset with only CHE
IMP_data_CHE <- IMP_data %>%
  filter(country == "CHE")

#melt to long format
IMP_data_CHE_m <- reshape2::melt(IMP_data_CHE, id.vars = c( "yearquarter", "country"), variable.name = "variable", value.name = "value")

#create groups
IMP_data_CHE_m$Instrument <- ordered( IMP_data_CHE_m$variable, levels = titles)

# set zero values to NA
IMP_data_CHE_m[IMP_data_CHE_m ==0] <- NA

# delete NAs
IMP_data_CHE_m <- IMP_data_CHE_m %>% filter(!is.na(Instrument))
IMP_data_CHE_m <- IMP_data_CHE_m %>% filter(!is.na(value))

# normalize CHE data manually to prevent error 'subscript out of bounds'
IMP_data_CHE_m_normalized <- IMP_data_CHE_m %>%
  group_by(yearquarter) %>%
  mutate(value_norm = value / sum(value)) %>%
  ungroup()


# Create an area plot with patterns
Switzerland <- ggplot(IMP_data_CHE_m_normalized, aes(y = value_norm, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1983,2023,10), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Switzerland") +
  theme_minimal()

Switzerland_stack <- ggplot(IMP_data_CHE_m, aes(y = value/1000, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1983,2023,10), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Switzerland") +
  theme_minimal()

#### France ####

# dataset with only FRA
IMP_data_FRA <- IMP_data %>%
  filter(country == "FRA")

#melt to long format
IMP_data_FRA_m <- reshape2::melt(IMP_data_FRA, id.vars = c( "yearquarter", "country"), variable.name = "variable", value.name = "value")

#create groups
IMP_data_FRA_m$Instrument <- ordered( IMP_data_FRA_m$variable, levels = titles)

# set zero values to NA
IMP_data_FRA_m[IMP_data_FRA_m ==0] <- NA

# delete NAs
IMP_data_FRA_m <- IMP_data_FRA_m %>% filter(!is.na(Instrument))
IMP_data_FRA_m <- IMP_data_FRA_m %>% filter(!is.na(value))

# normalize FRA data manually to prevent error 'subscript out of bounds'
IMP_data_FRA_m_normalized <- IMP_data_FRA_m %>%
  group_by(yearquarter) %>%
  mutate(value_norm = value / sum(value)) %>%
  ungroup()


# Create an area plot with patterns
France <- ggplot(IMP_data_FRA_m_normalized, aes(y = value_norm, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "France") +
  theme_minimal()


France_stack <- ggplot(IMP_data_FRA_m, aes(y = value/1000, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "France") +
  theme_minimal()


#### Japan ####

# dataset with only JPN
IMP_data_JPN <- IMP_data %>%
  filter(country == "JPN")

#melt to long format
IMP_data_JPN_m <- reshape2::melt(IMP_data_JPN, id.vars = c( "yearquarter", "country"), variable.name = "variable", value.name = "value")

#create groups
IMP_data_JPN_m$Instrument <- ordered( IMP_data_JPN_m$variable, levels = titles)

# set zero values to NA
IMP_data_JPN_m[IMP_data_JPN_m ==0] <- NA

# delete NAs
IMP_data_JPN_m <- IMP_data_JPN_m %>% filter(!is.na(Instrument))
IMP_data_JPN_m <- IMP_data_JPN_m %>% filter(!is.na(value))

# normalize JPN data manually to prevent error 'subscript out of bounds'
IMP_data_JPN_m_normalized <- IMP_data_JPN_m %>%
  group_by(yearquarter) %>%
  mutate(value_norm = value / sum(value)) %>%
  ungroup()


# Create an area plot with patterns
Japan <- ggplot(IMP_data_JPN_m_normalized, aes(y = value_norm, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1983,2023,10), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Japan") +
  theme_minimal()

Japan_stack <-ggplot(IMP_data_JPN_m, aes(y = value/1000, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1983,2023,10), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Japan") +
  theme_minimal()



#### Greece ####

# dataset with only GRC
IMP_data_GRC <- IMP_data %>%
  filter(country == "GRC")

#melt to long format
IMP_data_GRC_m <- reshape2::melt(IMP_data_GRC, id.vars = c( "yearquarter", "country"), variable.name = "variable", value.name = "value")

#create groups
IMP_data_GRC_m$Instrument <- ordered( IMP_data_GRC_m$variable,  levels = titles)

# set zero values to NA
IMP_data_GRC_m[IMP_data_GRC_m ==0] <- NA

# delete NAs
IMP_data_GRC_m <- IMP_data_GRC_m %>% filter(!is.na(Instrument))
IMP_data_GRC_m <- IMP_data_GRC_m %>% filter(!is.na(value))

# normalize GRC data manually to prevent error 'subscript out of bounds'
IMP_data_GRC_m_normalized <- IMP_data_GRC_m %>%
  group_by(yearquarter) %>%
  mutate(value_norm = value / sum(value)) %>%
  ungroup()


# Create an area plot with patterns
Greece <- ggplot(IMP_data_GRC_m_normalized, aes(y = value_norm, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Greece") +
  theme_minimal()

Greece_stack <-ggplot(IMP_data_GRC_m, aes(y = value/1000, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Greece") +
  theme_minimal()


#### South Korea ####

# dataset with only KOR
IMP_data_KOR <- IMP_data %>%
  filter(country == "KOR")

#melt to long format
IMP_data_KOR_m <- reshape2::melt(IMP_data_KOR, id.vars = c( "yearquarter", "country"), variable.name = "variable", value.name = "value")

#create groups
IMP_data_KOR_m$Instrument <- ordered( IMP_data_KOR_m$variable,levels = titles)

IMP_data_KOR_m[IMP_data_KOR_m ==0] <- NA

# delete NAs
IMP_data_KOR_m <- IMP_data_KOR_m %>% filter(!is.na(Instrument))
IMP_data_KOR_m <- IMP_data_KOR_m %>% filter(!is.na(value))


# normalize KOR data manually to prevent error 'subscript out of bounds'
IMP_data_KOR_m_normalized <- IMP_data_KOR_m %>%
  group_by(yearquarter) %>%
  mutate(value_norm = value / sum(value)) %>%
  ungroup()


# Create an area plot with patterns
South_Korea <- ggplot(IMP_data_KOR_m_normalized, aes(y = value_norm, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
    pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "South Korea") +
  theme_minimal()

South_Korea_stack <- ggplot(IMP_data_KOR_m, aes(y = value/1000, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "South Korea") +
  theme_minimal()


#### United Kingdom ####

# dataset with only GBR
IMP_data_GBR <- IMP_data %>%
  filter(country == "GBR")

#melt to long format
IMP_data_GBR_m <- reshape2::melt(IMP_data_GBR, id.vars = c( "yearquarter", "country"), variable.name = "variable", value.name = "value")

#create groups
IMP_data_GBR_m$Instrument <- ordered( IMP_data_GBR_m$variable, levels = titles)

# set zero values to NA
IMP_data_GBR_m[IMP_data_GBR_m ==0] <- NA

# delete NAs
IMP_data_GBR_m <- IMP_data_GBR_m %>% filter(!is.na(Instrument))
IMP_data_GBR_m <- IMP_data_GBR_m %>% filter(!is.na(value))
IMP_data_GBR_m <- IMP_data_GBR_m %>% filter(!is.na(yearquarter))
IMP_data_GBR_m <- IMP_data_GBR_m %>% filter(value != "0.00")

IMP_data_GBR_m <- IMP_data_GBR_m %>%
  complete(yearquarter, Instrument, fill = list(value = 0))


# Create an area plot with patterns

# normalize GBR data manually to prevent error 'subscript out of bounds'
IMP_data_GBR_m_normalized <- IMP_data_GBR_m %>%
  group_by(yearquarter) %>%
  mutate(value_norm = value / sum(value)) %>%
  ungroup()

# plot "filled" as "stack of normalized data" 
United_Kingdom <- ggplot(IMP_data_GBR_m_normalized, aes(y = value_norm, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
    pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1983,2023,10), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "United Kingdom") +
  theme_minimal()

United_Kingdom_stack <- ggplot(IMP_data_GBR_m, aes(y = value/1000, x = yearquarter)) +
  geom_area_pattern( aes(pattern = Instrument,  fill = Instrument), # Assign patterns by group
                     pattern_density = 0.1, 
                     pattern_spacing = 0.02,
                     pattern_key_scale_factor = 1,      position = "stack") +
  scale_pattern_manual(values = patterns) + # Define patterns for each group 
  scale_fill_manual(values = colours ) + # Define patterns for each group 
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1983,2023,10), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "United Kingdom") +
  theme_minimal()


#### now arrange all plots in one panel 

ggarrange(United_Kingdom,France, Germany, Greece, Japan,  South_Korea, Switzerland, Brazil, 
          ncol = 4, nrow = 2,
          # align = "v",
          widths = c(1.1,1),
          font.label = list(size = 11),
          common.legend = T, 
          legend = "bottom")
 
ggsave("Eurodollars_plot_fill.eps", path = folder, plot = last_plot(), width = 12, height = 6, dpi = 800)


# create Panel 1
ggarrange(United_Kingdom,France, Germany, Greece, Japan,  South_Korea, Switzerland, Brazil, 
           ncol = 2, nrow = 4,
           # align = "v",
           widths = c(1.1,1),
           font.label = list(size = 10),
           common.legend = T, 
           legend = "bottom")


ggsave("Eurodollars_plot_fill_hk.eps", path = folder, plot = last_plot(), width = 7, height = 12, dpi = 800)
ggsave("Panel_1.eps", path = folder, plot = last_plot(), width = 10, height = 12, dpi = 800)



### stacked for appendix


ggarrange(United_Kingdom_stack, France_stack, Germany_stack, Greece_stack, Japan_stack,  South_Korea_stack, Switzerland_stack, Brazil_stack, 
          ncol = 4, nrow = 2,
          # align = "v",
          widths = c(1.1,1),
          font.label = list(size = 10),
          common.legend = T, 
          legend = "bottom")


ggsave("Eurodollars_plot_stack.eps", path = folder, plot = last_plot(), width = 12, height = 6, dpi = 800)



ggarrange(United_Kingdom_stack, France_stack, Germany_stack, Greece_stack, Japan_stack,  South_Korea_stack, Switzerland_stack, Brazil_stack, 
          ncol = 2, nrow = 4,
          # align = "v",
          widths = c(1.1,1),
          font.label = list(size = 10),
          common.legend = T, 
          legend = "bottom")


ggsave("Eurodollars_plot_stack_hk.eps", path = folder, plot = last_plot(), width = 7, height = 12, dpi = 800)













# +---------------------+
# | Eurodollars by OFCs |
# +---------------------+


colours_ofcs <- c(   "#E0E0E0", "#C0C0C0","#808080","#606060", "#202020")


### Brazil

# Create an empty data frame to store the aggregated values
aggregated_df_ofc_BRA <- data.frame(iso3c_ofc = character(0), yearquarter = numeric(0), claims_ofc = numeric(0), liabs_ofc = numeric(0))

# Loop over the OFCs
for (ofc in offshorelist) {
  # Find ofc
  LBS_ofc_BRA <- LBSstocks_bilateral[LBSstocks_bilateral$iso3c_cparty == ofc & LBSstocks_bilateral$iso3c == "BRA", ]
  
  # Shorten
  LBS_ofc_BRA <- LBS_ofc_BRA[, c(1:4, 7)]
  
  # Rename (we use only liabs)
  LBS_ofc_BRA$liabs_ofc <- LBS_ofc_BRA$liabs_all_all
  LBS_ofc_BRA$claims_ofc <- LBS_ofc_BRA$claims_all_all
  
  # Aggregate all countries
  if (nrow(LBS_ofc_BRA) > 0) {
    LBS_ofc_BRA <- aggregate(cbind(claims_ofc, liabs_ofc) ~ yearquarter, data = LBS_ofc_BRA, FUN = sum)
  } else {
    cat("No data to aggregate for ofc:", ofc, "\n")
  }
  
  # Add column that gives ofc name
  LBS_ofc_BRA <- mutate(LBS_ofc_BRA, iso3c_ofc = rep(ofc, nrow(LBS_ofc_BRA)))
  LBS_ofc_BRA <- LBS_ofc_BRA[, c(4, 1, 2, 3)]
  
  # Append the yearquarter data to the aggregated data frame
  aggregated_df_ofc_BRA <- dplyr::bind_rows(aggregated_df_ofc_BRA, LBS_ofc_BRA)
}

# Add iso3c column for Brazil
aggregated_df_ofc_BRA$iso3c <- "BRA"
aggregated_df_ofc_BRA <- aggregated_df_ofc_BRA[, c(6, 1, 2, 4)]
rm(LBS_ofc_BRA)

# Convert yearquarter to numeric before aggregation
aggregated_df_ofc_BRA$yearquarter <- as.numeric(aggregated_df_ofc_BRA$yearquarter)



### Japan

# Create an empty data frame to store the aggregated values
aggregated_df_ofc_JPN <- data.frame(iso3c_ofc = character(0), yearquarter = numeric(0), claims_ofc = numeric(0), liabs_ofc = numeric(0))

# Loop over the OFCs
for (ofc in offshorelist) {
  # Find ofc
  LBS_ofc_JPN <- LBSstocks_bilateral[LBSstocks_bilateral$iso3c_cparty == ofc & LBSstocks_bilateral$iso3c == "JPN", ]
  
  # Shorten
  LBS_ofc_JPN <- LBS_ofc_JPN[, c(1:4, 7)]
  
  # Rename (we use only liabs)
  LBS_ofc_JPN$liabs_ofc <- LBS_ofc_JPN$liabs_all_all
  LBS_ofc_JPN$claims_ofc <- LBS_ofc_JPN$claims_all_all
  
  # Aggregate all countries
  if (nrow(LBS_ofc_JPN) > 0) {
    LBS_ofc_JPN <- aggregate(cbind(claims_ofc, liabs_ofc) ~ yearquarter, data = LBS_ofc_JPN, FUN = sum)
  } else {
    cat("No data to aggregate for ofc:", ofc, "\n")
  }
  
  # Add column that gives ofc name
  LBS_ofc_JPN <- mutate(LBS_ofc_JPN, iso3c_ofc = rep(ofc, nrow(LBS_ofc_JPN)))
  LBS_ofc_JPN <- LBS_ofc_JPN[, c(4, 1, 2, 3)]
  
  # Append the yearquarter data to the aggregated data frame
  aggregated_df_ofc_JPN <- dplyr::bind_rows(aggregated_df_ofc_JPN, LBS_ofc_JPN)
}

# Add iso3c column for Japan
aggregated_df_ofc_JPN$iso3c <- "JPN"
aggregated_df_ofc_JPN <- aggregated_df_ofc_JPN[, c(6, 1, 2, 4)]
rm(LBS_ofc_JPN)

# Convert yearquarter to numeric before aggregation
aggregated_df_ofc_JPN$yearquarter <- as.numeric(aggregated_df_ofc_JPN$yearquarter)



### United Kingdom

# Create an empty data frame to store the aggregated values
aggregated_df_ofc_GBR <- data.frame(iso3c_ofc = character(0), yearquarter = numeric(0), claims_ofc = numeric(0), liabs_ofc = numeric(0))

# Loop over the OFCs
for (ofc in offshorelist) {
  # Find ofc
  LBS_ofc_GBR <- LBSstocks_bilateral[LBSstocks_bilateral$iso3c_cparty == ofc & LBSstocks_bilateral$iso3c == "GBR", ]
  
  # Shorten
  LBS_ofc_GBR <- LBS_ofc_GBR[, c(1:4, 7)]
  
  # Rename (we use only liabs)
  LBS_ofc_GBR$liabs_ofc <- LBS_ofc_GBR$liabs_all_all
  LBS_ofc_GBR$claims_ofc <- LBS_ofc_GBR$claims_all_all
  
  # Aggregate all countries
  if (nrow(LBS_ofc_GBR) > 0) {
    LBS_ofc_GBR <- aggregate(cbind(claims_ofc, liabs_ofc) ~ yearquarter, data = LBS_ofc_GBR, FUN = sum)
  } else {
    cat("No data to aggregate for ofc:", ofc, "\n")
  }
  
  # Add column that gives ofc name
  LBS_ofc_GBR <- mutate(LBS_ofc_GBR, iso3c_ofc = rep(ofc, nrow(LBS_ofc_GBR)))
  LBS_ofc_GBR <- LBS_ofc_GBR[, c(4, 1, 2, 3)]
  
  # Append the yearquarter data to the aggregated data frame
  aggregated_df_ofc_GBR <- dplyr::bind_rows(aggregated_df_ofc_GBR, LBS_ofc_GBR)
}

# Add iso3c column for the United Kingdom
aggregated_df_ofc_GBR$iso3c <- "GBR"
aggregated_df_ofc_GBR <- aggregated_df_ofc_GBR[, c(6, 1, 2, 4)]
rm(LBS_ofc_GBR)

# Convert yearquarter to numeric before aggregation
aggregated_df_ofc_GBR$yearquarter <- as.numeric(aggregated_df_ofc_GBR$yearquarter)





### Germany


# Create an empty data frame to store the aggregated values
aggregated_df_ofc_DEU <- data.frame(iso3c_ofc = character(0), yearquarter = numeric(0), claims_ofc = numeric(0), liabs_ofc = numeric(0))

# Loop over the OFCs
for (ofc in offshorelist) {
  # Filter data for Germany and current OFC
  LBS_ofc_DEU <- LBSstocks_bilateral[LBSstocks_bilateral$iso3c_cparty == ofc & LBSstocks_bilateral$iso3c == "DEU", ]
  
  # Check if there are rows to process
  if (nrow(LBS_ofc_DEU) == 0) {
    cat("No data to aggregate for OFC:", ofc, "in DEU\n")
    next  # Skip to the next OFC if no rows found
  }
  
  # Ensure that there are no missing values in the relevant columns
  LBS_ofc_DEU <- LBS_ofc_DEU[complete.cases(LBS_ofc_DEU[, c("liabs_all_all", "claims_all_all", "yearquarter")]), ]
  
  # Double-check if there are rows to aggregate after removing NAs
  if (nrow(LBS_ofc_DEU) == 0) {
    cat("No valid data to aggregate for OFC:", ofc, "in DEU after removing NAs\n")
    next
  }
  
  # Shorten the data frame to the necessary columns
  LBS_ofc_DEU <- LBS_ofc_DEU[, c(1:4, 7)]
  
  # Rename columns for clarity
  LBS_ofc_DEU$liabs_ofc <- LBS_ofc_DEU$liabs_all_all
  LBS_ofc_DEU$claims_ofc <- LBS_ofc_DEU$claims_all_all
  
  # Convert yearquarter to numeric if it isn't already
  LBS_ofc_DEU$yearquarter <- as.numeric(LBS_ofc_DEU$yearquarter)
  
  # Check for valid numeric yearquarter values after conversion
  if (any(is.na(LBS_ofc_DEU$yearquarter))) {
    cat("Invalid yearquarter values for OFC:", ofc, "in DEU after conversion\n")
    next
  }
  
  # Aggregate the data for claims and liabilities by yearquarter
  LBS_ofc_DEU <- aggregate(cbind(claims_ofc, liabs_ofc) ~ yearquarter, data = LBS_ofc_DEU, FUN = sum)
  
  # If after aggregation, no rows are found, print an error and continue
  if (nrow(LBS_ofc_DEU) == 0) {
    cat("No data to aggregate for OFC:", ofc, "in DEU after aggregation\n")
    next
  }
  
  # Add column that gives OFC name
  LBS_ofc_DEU <- mutate(LBS_ofc_DEU, iso3c_ofc = rep(ofc, nrow(LBS_ofc_DEU)))
  
  # Rearrange columns
  LBS_ofc_DEU <- LBS_ofc_DEU[, c(4, 1, 2, 3)]
  
  # Append the yearquarter data to the aggregated data frame
  aggregated_df_ofc_DEU <- dplyr::bind_rows(aggregated_df_ofc_DEU, LBS_ofc_DEU)
}

# Add iso3c column for Germany
aggregated_df_ofc_DEU$iso3c <- "DEU"

# Rearrange columns to the final desired order
aggregated_df_ofc_DEU <- aggregated_df_ofc_DEU[, c(5, 1, 2, 4)]

# Cleanup
rm(LBS_ofc_DEU)

# Convert yearquarter to numeric before any further operations, if needed
aggregated_df_ofc_DEU$yearquarter <- as.numeric(aggregated_df_ofc_DEU$yearquarter)



### Korea 



# Create an empty data frame to store the aggregated values
aggregated_df_ofc_KOR <- data.frame(iso3c_ofc = character(0), yearquarter = numeric(0), claims_ofc = numeric(0), liabs_ofc = numeric(0))

# Loop over the OFCs
for (ofc in offshorelist) {
  # Find ofc
  LBS_ofc_KOR <- LBSstocks_bilateral[LBSstocks_bilateral$iso3c_cparty == ofc & LBSstocks_bilateral$iso3c == "KOR", ]
  
  # Shorten
  LBS_ofc_KOR <- LBS_ofc_KOR[, c(1:4, 7)]
  
  # Rename (we use only liabs)
  LBS_ofc_KOR$liabs_ofc <- LBS_ofc_KOR$liabs_all_all
  LBS_ofc_KOR$claims_ofc <- LBS_ofc_KOR$claims_all_all
  
  # Aggregate all countries
  if (nrow(LBS_ofc_KOR) > 0) {
    LBS_ofc_KOR <- aggregate(cbind(claims_ofc, liabs_ofc) ~ yearquarter, data = LBS_ofc_KOR, FUN = sum)
  } else {
    cat("No data to aggregate for ofc:", ofc, "\n")
  }
  
  # Add column that gives ofc name
  LBS_ofc_KOR <- mutate(LBS_ofc_KOR, iso3c_ofc = rep(ofc, nrow(LBS_ofc_KOR)))
  LBS_ofc_KOR <- LBS_ofc_KOR[, c(4, 1, 2, 3)]
  
  # Append the yearquarter data to the aggregated data frame
  aggregated_df_ofc_KOR <- dplyr::bind_rows(aggregated_df_ofc_KOR, LBS_ofc_KOR)
}

# Add iso3c column for Korea
aggregated_df_ofc_KOR$iso3c <- "KOR"
aggregated_df_ofc_KOR <- aggregated_df_ofc_KOR[, c(6, 1, 2, 4)]
rm(LBS_ofc_KOR)

# Convert yearquarter to numeric before aggregation
aggregated_df_ofc_KOR$yearquarter <- as.numeric(aggregated_df_ofc_KOR$yearquarter)




### Switzerland 

# Create an empty data frame to store the aggregated values
aggregated_df_ofc_CHE <- data.frame(iso3c_ofc = character(0), yearquarter = numeric(0), claims_ofc = numeric(0), liabs_ofc = numeric(0))

# Loop over the OFCs
for (ofc in offshorelist) {
  # Find ofc
  LBS_ofc_CHE <- LBSstocks_bilateral[LBSstocks_bilateral$iso3c_cparty == ofc & LBSstocks_bilateral$iso3c == "CHE", ]
  
  # Shorten
  LBS_ofc_CHE <- LBS_ofc_CHE[, c(1:4, 7)]
  
  # Rename (we use only liabs)
  LBS_ofc_CHE$liabs_ofc <- LBS_ofc_CHE$liabs_all_all
  LBS_ofc_CHE$claims_ofc <- LBS_ofc_CHE$claims_all_all
  
  # Aggregate all countries
  if (nrow(LBS_ofc_CHE) > 0) {
    LBS_ofc_CHE <- aggregate(cbind(claims_ofc, liabs_ofc) ~ yearquarter, data = LBS_ofc_CHE, FUN = sum)
  } else {
    cat("No data to aggregate for ofc:", ofc, "\n")
  }
  
  # Add column that gives ofc name
  LBS_ofc_CHE <- mutate(LBS_ofc_CHE, iso3c_ofc = rep(ofc, nrow(LBS_ofc_CHE)))
  LBS_ofc_CHE <- LBS_ofc_CHE[, c(4, 1, 2, 3)]
  
  # Append the yearquarter data to the aggregated data frame
  aggregated_df_ofc_CHE <- dplyr::bind_rows(aggregated_df_ofc_CHE, LBS_ofc_CHE)
}

# Add iso3c column for Switzerland
aggregated_df_ofc_CHE$iso3c <- "CHE"
aggregated_df_ofc_CHE <- aggregated_df_ofc_CHE[, c(6, 1, 2, 4)]
rm(LBS_ofc_CHE)

# Convert yearquarter to numeric before aggregation
aggregated_df_ofc_CHE$yearquarter <- as.numeric(aggregated_df_ofc_CHE$yearquarter)



### France 

# Create an empty data frame to store the aggregated values

aggregated_df_ofc_FRA <- data.frame(iso3c_ofc = character(0), yearquarter = numeric(0), claims_ofc = numeric(0), liabs_ofc = numeric(0))

# Loop over the OFCs
for (ofc in offshorelist) {
  # Find ofc
  LBS_ofc_FRA <- LBSstocks_bilateral[LBSstocks_bilateral$iso3c_cparty == ofc & LBSstocks_bilateral$iso3c == "FRA", ]
  
  # Shorten
  LBS_ofc_FRA <- LBS_ofc_FRA[, c(1:4, 7)]
  
  # Rename (we use only liabs)
  LBS_ofc_FRA$liabs_ofc <- LBS_ofc_FRA$liabs_all_all
  LBS_ofc_FRA$claims_ofc <- LBS_ofc_FRA$claims_all_all
  
  # Aggregate all countries
  if (nrow(LBS_ofc_FRA) > 0) {
    LBS_ofc_FRA <- aggregate(cbind(claims_ofc, liabs_ofc) ~ yearquarter, data = LBS_ofc_FRA, FUN = sum)
  } else {
    cat("No data to aggregate for ofc:", ofc, "\n")
  }
  
  # Add column that gives ofc name
  LBS_ofc_FRA <- mutate(LBS_ofc_FRA, iso3c_ofc = rep(ofc, nrow(LBS_ofc_FRA)))
  LBS_ofc_FRA <- LBS_ofc_FRA[, c(4, 1, 2, 3)]
  
  # Append the yearquarter data to the aggregated data frame
  aggregated_df_ofc_FRA <- dplyr::bind_rows(aggregated_df_ofc_FRA, LBS_ofc_FRA)
}

# Add iso3c column for France
aggregated_df_ofc_FRA$iso3c <- "FRA"
aggregated_df_ofc_FRA <- aggregated_df_ofc_FRA[, c(5, 1, 2, 4)]
rm(LBS_ofc_FRA)

# Convert yearquarter to numeric before aggregation
aggregated_df_ofc_FRA$yearquarter <- as.numeric(aggregated_df_ofc_FRA$yearquarter)




### Greece 

# Create an empty data frame to store the aggregated values
aggregated_df_ofc_GRC <- data.frame(iso3c_ofc = character(0), yearquarter = numeric(0), claims_ofc = numeric(0), liabs_ofc = numeric(0))

# Loop over the OFCs
for (ofc in offshorelist) {
  # Find ofc
  LBS_ofc_GRC <- LBSstocks_bilateral[LBSstocks_bilateral$iso3c_cparty == ofc & LBSstocks_bilateral$iso3c == "GRC", ]
  
  # Shorten
  LBS_ofc_GRC <- LBS_ofc_GRC[, c(1:4, 7)]
  
  # Rename (we use only liabs)
  LBS_ofc_GRC$liabs_ofc <- LBS_ofc_GRC$liabs_all_all
  LBS_ofc_GRC$claims_ofc <- LBS_ofc_GRC$claims_all_all
  
  # Aggregate all countries
  if (nrow(LBS_ofc_GRC) > 0) {
    LBS_ofc_GRC <- aggregate(cbind(claims_ofc, liabs_ofc) ~ yearquarter, data = LBS_ofc_GRC, FUN = sum)
  } else {
    cat("No data to aggregate for ofc:", ofc, "\n")
  }
  
  # Add column that gives ofc name
  LBS_ofc_GRC <- mutate(LBS_ofc_GRC, iso3c_ofc = rep(ofc, nrow(LBS_ofc_GRC)))
  LBS_ofc_GRC <- LBS_ofc_GRC[, c(4, 1, 2, 3)]
  
  # Append the yearquarter data to the aggregated data frame
  aggregated_df_ofc_GRC <- dplyr::bind_rows(aggregated_df_ofc_GRC, LBS_ofc_GRC)
}

# Add iso3c column for Greece
aggregated_df_ofc_GRC$iso3c <- "GRC"
aggregated_df_ofc_GRC <- aggregated_df_ofc_GRC[, c(6, 1, 2, 4)]
rm(LBS_ofc_GRC)

# Convert yearquarter to numeric before aggregation
aggregated_df_ofc_GRC$yearquarter <- as.numeric(aggregated_df_ofc_GRC$yearquarter)














##### PLOTTING ####





### plotting Brazil by OFCs


unique(aggregated_df_ofc_BRA$iso3c_ofc)

#calculate sum over all years 
LBS_offshore_eversum_BRA <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  aggregated_df_ofc_BRA, sum)

# Identify the 15 categories with the smallest values
small_categories_BRA <- LBS_offshore_eversum_BRA %>%
  group_by(iso3c_ofc) %>%
  summarise(total_value = sum(liabs_ofc)) %>%
  arrange(total_value) %>%
  head(16) %>%
  pull(iso3c_ofc)

library(forcats)
# Replace those categories with "Others"
BRA_ofc_plottable <- aggregated_df_ofc_BRA %>%
  mutate(iso3c_ofc = fct_collapse(iso3c_ofc, Others = small_categories_BRA))

#aggregate all in others 
BRA_ofc_plottable <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  BRA_ofc_plottable, sum)


# rename so that we see the biggest OFCs and the rest is "others"
unique(BRA_ofc_plottable$iso3c_ofc)
BRA_ofc_plottable$offshore_center<- ordered( BRA_ofc_plottable$iso3c_ofc, levels = c( "CYM",  "BHS", "GBR","NLD",  "Others"))

# make time numeric 
BRA_ofc_plottable$yearquarter <- as.numeric (BRA_ofc_plottable$yearquarter)

# plot 
Brazil_OFC_fill <- ggplot(BRA_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "fill") +
  # scale_pattern_manual(values = patterns_ofcs) + # Define patterns for each group 
  scale_fill_manual(values = colours_ofcs, labels = c ("Caymans", "Bahamas", "United Kingdom", "Netherlands", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Brazil") +
  theme_minimal()

Brazil_OFC_stack <- ggplot(BRA_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "stack") +
  # scale_pattern_manual(values = patterns_ofcs) + # Define patterns for each group 
  scale_fill_manual(values = colours_ofcs, labels = c ("Caymans", "Bahamas", "United Kingdom", "Netherlands", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Brazil") +
  theme_minimal()



### plotting France by OFCs


unique(aggregated_df_ofc_FRA$iso3c_ofc)

#calculate sum over all years 
LBS_offshore_eversum_FRA <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  aggregated_df_ofc_FRA, sum)

# Identify the 15 categories with the smallest values
small_categories_FRA <- LBS_offshore_eversum_FRA %>%
  group_by(iso3c_ofc) %>%
  summarise(total_value = sum(liabs_ofc)) %>%
  arrange(total_value) %>%
  head(24) %>%
  pull(iso3c_ofc)


# Replace those categories with "Others"
FRA_ofc_plottable <- aggregated_df_ofc_FRA %>%
  mutate(iso3c_ofc = fct_collapse(iso3c_ofc, Others = small_categories_FRA))

unique(FRA_ofc_plottable$iso3c_ofc)

#aggregate all in others 
FRA_ofc_plottable <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  FRA_ofc_plottable, sum)


# rename so that we see the biggest OFCs and the rest is "others"
unique(FRA_ofc_plottable$iso3c_ofc)
FRA_ofc_plottable$offshore_center<- ordered( FRA_ofc_plottable$iso3c_ofc, levels = c( "GBR", "LUX", "NLD", "CHE",  "Others"))

# make time numeric 
FRA_ofc_plottable$yearquarter <- as.numeric (FRA_ofc_plottable$yearquarter)

# plot 
France_OFC_fill <- ggplot(FRA_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "fill") +
  # scale_pattern_manual(values = patterns_ofcs) + # Define patterns for each group 
  scale_fill_manual(values = colours_ofcs, labels = c ("United Kingdom", "Luxemburg", "Netherlands", "Switzerland", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "France") +
  theme_minimal()

France_OFC_stack <- ggplot(FRA_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "stack") +
  scale_fill_manual(values = colours_ofcs, labels = c ("United Kingdom", "Luxemburg", "Netherlands", "Switzerland", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "France") +
  theme_minimal()






### plotting United Kingdom by OFCs


unique(aggregated_df_ofc_GBR$iso3c_ofc)

#calculate sum over all years 
LBS_offshore_eversum_GBR <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  aggregated_df_ofc_GBR, sum)

# Identify the 15 categories with the smallest values
small_categories_GBR <- LBS_offshore_eversum_GBR %>%
  group_by(iso3c_ofc) %>%
  summarise(total_value = sum(liabs_ofc)) %>%
  arrange(total_value) %>%
  head(21) %>%
  pull(iso3c_ofc)


# Replace those categories with "Others"
GBR_ofc_plottable <- aggregated_df_ofc_GBR %>%
  mutate(iso3c_ofc = fct_collapse(iso3c_ofc, Others = small_categories_GBR))

#aggregate all in others 
GBR_ofc_plottable <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  GBR_ofc_plottable, sum)

unique(GBR_ofc_plottable$iso3c_ofc)

# rename so that we see the biggest OFCs and the rest is "others"
unique(GBR_ofc_plottable$iso3c_ofc)
GBR_ofc_plottable$offshore_center<- ordered( GBR_ofc_plottable$iso3c_ofc, levels = c( "CHE", "IRL", "NLD", "JEY",  "Others"))

# make time numeric 
GBR_ofc_plottable$yearquarter <- as.numeric (GBR_ofc_plottable$yearquarter)

# plot 
Britain_OFC_fill <- ggplot(GBR_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "fill") +
  scale_fill_manual(values = colours_ofcs, labels = c ("Switzerland", "Ireland", "Netherlands", "Jersey", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "United Kingdom") +
  theme_minimal()

Britain_OFC_stack<- ggplot(GBR_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "stack") +
  scale_fill_manual(values = colours_ofcs, labels = c ("Switzerland", "Ireland", "Netherlands", "Jersey", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "United Kingdom") +
  theme_minimal()





### plotting Switzerland by OFCs


unique(aggregated_df_ofc_CHE$iso3c_ofc)

#calculate sum over all years 
LBS_offshore_eversum_CHE <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  aggregated_df_ofc_CHE, sum)

# Identify the 15 categories with the smallest values
small_categories_CHE <- LBS_offshore_eversum_CHE %>%
  group_by(iso3c_ofc) %>%
  summarise(total_value = sum(liabs_ofc)) %>%
  arrange(total_value) %>%
  head(20) %>%
  pull(iso3c_ofc)


# Replace those categories with "Others"
CHE_ofc_plottable <- aggregated_df_ofc_CHE %>%
  mutate(iso3c_ofc = fct_collapse(iso3c_ofc, Others = small_categories_CHE))

#aggregate all in others 
CHE_ofc_plottable <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  CHE_ofc_plottable, sum)


# rename so that we see the biggest OFCs and the rest is "others"
unique(CHE_ofc_plottable$iso3c_ofc)
CHE_ofc_plottable$offshore_center<- ordered( CHE_ofc_plottable$iso3c_ofc, levels = c( "GBR", "LUX", "SGP", "BHS",  "Others"))

# make time numeric 
CHE_ofc_plottable$yearquarter <- as.numeric (CHE_ofc_plottable$yearquarter)

# plot 
Switzerland_OFC_fill <- ggplot(CHE_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "fill") +
  scale_fill_manual(values = colours_ofcs, labels = c ("United Kingdom", "Luxemburg", "Singapore", "Bahamas", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Switzerland") +
  theme_minimal()

Switzerland_OFC_stack <- ggplot(CHE_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "stack") +
  scale_fill_manual(values = colours_ofcs, labels = c ("United Kingdom", "Luxemburg", "Singapore", "Bahamas", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Switzerland") +
  theme_minimal()



### plotting Korea by OFCs


unique(aggregated_df_ofc_KOR$iso3c_ofc)

#calculate sum over all years 
LBS_offshore_eversum_KOR <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  aggregated_df_ofc_KOR, sum)

# Identify the 15 categories with the smallest values
small_categories_KOR <- LBS_offshore_eversum_KOR %>%
  group_by(iso3c_ofc) %>%
  summarise(total_value = sum(liabs_ofc)) %>%
  arrange(total_value) %>%
  head(23) %>%
  pull(iso3c_ofc)


# Replace those categories with "Others"
KOR_ofc_plottable <- aggregated_df_ofc_KOR %>%
  mutate(iso3c_ofc = fct_collapse(iso3c_ofc, Others = small_categories_KOR))

#aggregate all in others 
KOR_ofc_plottable <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  KOR_ofc_plottable, sum)


# rename so that we see the biggest OFCs and the rest is "others"
unique(KOR_ofc_plottable$iso3c_ofc)
KOR_ofc_plottable$offshore_center<- ordered( KOR_ofc_plottable$iso3c_ofc, levels = c( "HKG",  "GBR", "SGP","CHE", "Others"))

# make time numeric 
KOR_ofc_plottable$yearquarter <- as.numeric (KOR_ofc_plottable$yearquarter)

# plot 
Korea_OFC_fill <- ggplot(KOR_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "fill") +
  scale_fill_manual(values = colours_ofcs, labels = c ("Hong Kong", "United Kingdom", "Singapore",  "Switzerland", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "South Korea") +
  theme_minimal()

Korea_OFC_stack <- ggplot(KOR_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "stack") +
  scale_fill_manual(values = colours_ofcs, labels = c ("Hong Kong",  "United Kingdom","Singapore", "Switzerland", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "South Korea") +
  theme_minimal()




### plotting Japan by OFCs


unique(aggregated_df_ofc_JPN$iso3c_ofc)

#calculate sum over all years 
LBS_offshore_eversum_JPN <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  aggregated_df_ofc_JPN, sum)

# Identify the 15 categories with the smallest values
small_categories_JPN <- LBS_offshore_eversum_JPN %>%
  group_by(iso3c_ofc) %>%
  summarise(total_value = sum(liabs_ofc)) %>%
  arrange(total_value) %>%
  head(10) %>%
  pull(iso3c_ofc)


# Replace those categories with "Others"
JPN_ofc_plottable <- aggregated_df_ofc_JPN %>%
  mutate(iso3c_ofc = fct_collapse(iso3c_ofc, Others = small_categories_JPN))

#aggregate all in others 
JPN_ofc_plottable <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  JPN_ofc_plottable, sum)


# rename so that we see the biggest OFCs and the rest is "others"
unique(JPN_ofc_plottable$iso3c_ofc)
JPN_ofc_plottable$offshore_center<- ordered( JPN_ofc_plottable$iso3c_ofc, levels = c("GBR",  "HKG", "SGP", "CYM", "Others"))

# make time numeric 
JPN_ofc_plottable$yearquarter <- as.numeric (JPN_ofc_plottable$yearquarter)

# plot 
Japan_OFC_fill <- ggplot(JPN_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "fill") +
  scale_fill_manual(values = colours_ofcs, labels = c ("United Kingdom", "Hong Kong", "Singapore", "Caymans", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Japan") +
  theme_minimal()

Japan_OFC_stack <- ggplot(JPN_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "stack") +
  scale_fill_manual(values = colours_ofcs, labels = c ("United Kingdom", "Hong Kong", "Singapore", "Caymans", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Japan") +
  theme_minimal()






### plotting Germany by OFCs


unique(aggregated_df_ofc_DEU$iso3c_ofc)

#calculate sum over all years 
LBS_offshore_eversum_DEU <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  aggregated_df_ofc_DEU, sum)

#LBS_offshore_eversum_DEU_claims <- aggregate (claims_ofc ~ iso3c_ofc + yearquarter,  aggregated_df_ofc_DEU, sum)

# Identify the 15 categories with the smallest values
small_categories_DEU <- LBS_offshore_eversum_DEU %>%
  group_by(iso3c_ofc) %>%
  summarise(total_value = sum(liabs_ofc)) %>%
  arrange(total_value) %>%
  head(19) %>%
  pull(iso3c_ofc)




# Replace those categories with "Others"
DEU_ofc_plottable <- aggregated_df_ofc_DEU %>%
  mutate(iso3c_ofc = fct_collapse(iso3c_ofc, Others = small_categories_DEU))

#aggregate all in others 
DEU_ofc_plottable <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  DEU_ofc_plottable, sum)

# rename so that we see the biggest OFCs and the rest is "others"
unique(DEU_ofc_plottable$iso3c_ofc)
DEU_ofc_plottable$offshore_center<- ordered( DEU_ofc_plottable$iso3c_ofc, levels = c( "GBR", "LUX", "CHE", "NLD",  "Others"))



# make time numeric 
DEU_ofc_plottable$yearquarter <- as.numeric (DEU_ofc_plottable$yearquarter)

# plot 
Germany_OFC_fill <- ggplot(DEU_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "fill") +
  scale_fill_manual(values = colours_ofcs, labels = c ("United Kingdom", "Luxemburg", "Switzerland", "Netherlands", "Others"),name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Germany") +
  theme_minimal()



Germany_OFC_stack <- ggplot(DEU_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "stack") +
  scale_fill_manual(values = colours_ofcs, labels = c ("United Kingdom", "Luxemburg", "Switzerland", "Netherlands", "Others"), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
  scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1980,2023,1)) +
  labs(y = NULL, x = NULL, title = "Liabilities of Germany") +
  theme_minimal()







### plotting Greece by OFCs


unique(aggregated_df_ofc_GRC$iso3c_ofc)

#calculate sum over all years 
LBS_offshore_eversum_GRC <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  aggregated_df_ofc_GRC, sum)

# Identify the 15 categories with the smallest values
small_categories_GRC <- LBS_offshore_eversum_GRC %>%
  group_by(iso3c_ofc) %>%
  summarise(total_value = sum(liabs_ofc)) %>%
  arrange(total_value) %>%
  head(13) %>%
  pull(iso3c_ofc)


# Replace those categories with "Others"
GRC_ofc_plottable <- aggregated_df_ofc_GRC %>%
  mutate(iso3c_ofc = fct_collapse(iso3c_ofc, Others = small_categories_GRC))

#aggregate all in others 
GRC_ofc_plottable <- aggregate (liabs_ofc ~ iso3c_ofc + yearquarter,  GRC_ofc_plottable, sum)


# rename so that we see the biggest OFCs and the rest is "others"
unique(GRC_ofc_plottable$iso3c_ofc)
GRC_ofc_plottable$offshore_center<- ordered( GRC_ofc_plottable$iso3c_ofc, levels = c("CYP", "GBR","NLD",  "LUX",   "Others"))

# make time numeric 
GRC_ofc_plottable$yearquarter <- as.numeric (GRC_ofc_plottable$yearquarter)

# plot 
Greece_OFC_fill <-ggplot(GRC_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "fill") +
  scale_fill_manual(values = colours_ofcs, labels = c ("Cyprus", "United Kingdom", "Netherlands", "Luxembourg", "Others" ), name = "") +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Greece") +
  theme_minimal()

Greece_OFC_stack <- ggplot(GRC_ofc_plottable, aes(y = liabs_ofc/1000, x = yearquarter)) +
  geom_area( aes( fill = offshore_center), # Assign patterns by group
             position = "stack") +
  scale_fill_manual(values = colours_ofcs, labels = c ("Cyprus", "United Kingdom", "Netherlands", "Luxembourg", "Others"), name = "" ) +
  scale_y_continuous(n.breaks = 5, labels = function(x) format (x, scientific = F)) +
scale_x_continuous(breaks = seq(1978,2023,5), minor_breaks = seq(1978,2023,1)) +
  labs(y = NULL, x = NULL, title = "Greece") +
  theme_minimal()






#### now arrange all plots in one panel 


# filled graphs (for appendix)

# create Panel 2
ggarrange(Britain_OFC_fill, France_OFC_fill, Germany_OFC_fill, Greece_OFC_fill, Japan_OFC_fill,  Korea_OFC_fill, Switzerland_OFC_fill, Brazil_OFC_fill, 
          ncol = 2, nrow = 4,
          # align = "v",
          widths = c(1.1,1),
          font.label = list(size = 8),
          common.legend = F, 
          legend = "right")

ggsave("Eurodollars_by_OFCs_filled_plot_HK.eps", path = folder, plot = last_plot(), width = 10, height = 12, dpi = 800)
ggsave("Panel_2.eps", path = folder, plot = last_plot(), width = 10, height = 12, dpi = 800)


# stacked graphs (for appendix)

ggarrange(Britain_OFC_stack, France_OFC_stack, Germany_OFC_stack, Greece_OFC_stack, Japan_OFC_stack,  Korea_OFC_stack,  Switzerland_OFC_stack,  Brazil_OFC_stack,
          ncol = 2, nrow = 4,
          # align = "v",
          widths = c(1.1,1),
          font.label = list(size = 8),
          common.legend = F, 
          legend = "right")

  
ggsave("Eurodollars_by_OFCs_stacked_plot.eps", path = folder, plot = last_plot(), width = 12, height = 12, dpi = 800)


ggarrange(Britain_OFC_stack, France_OFC_stack, Germany_OFC_stack, Greece_OFC_stack, Japan_OFC_stack,  Korea_OFC_stack,  Switzerland_OFC_stack,  Brazil_OFC_stack,
          ncol = 2, nrow = 4,
          # align = "v",
          widths = c(1.1,1),
          font.label = list(size = 8),
          common.legend = F, 
          legend = "right")


ggsave("Eurodollars_by_OFCs_stacked_plot_hk.eps", path = folder, plot = last_plot(), width = 10, height = 12, dpi = 800)















